﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

/// <summary>
/// Summary description for AddminMethord
/// </summary>
public class AddminMethord
{
    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
	public AddminMethord()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    public void connClose()
    {
        if (conn.State == ConnectionState.Open)
        {
            conn.Close();
        }
    }
    private void connOpen()
    {
        if (conn.State == ConnectionState.Closed)
        {
            conn.Open();
        }
    }
    public DataTable AdminViewallHR()
    {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand sqlcm = new SqlCommand("SELECT * FROM vw_AdminviewallHR", conn);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(sqlcm);
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception ex)
        {

        }
        finally 
        {
            connClose();
        }
        return tb;
    }

    public DataTable AdminSearchHR(string _username) {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand sqlcom = new SqlCommand("EXEC AdminSearchHR @UserName", conn);
            sqlcom.Parameters.AddWithValue("@UserName", _username);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(sqlcom);
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception ex)
        {
            
        }
        finally 
        {
            connClose();
        }
        return tb;
    }
    public DataTable AdminSearchHrbyFulName(string _fullname) {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand sqlcmm = new SqlCommand("EXEC AdminSearchHRbyFullName @FullName", conn);
            sqlcmm.Parameters.AddWithValue("@FullName", _fullname);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(sqlcmm);
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception ex)
        {

            throw;
        }
        finally {
            connClose();
        }
        return tb;
    }
    public DataTable AdminviewAllInterviewer() {
        DataTable tb = new DataTable();
        try
        {
            connOpen();
            SqlCommand sqlcom = new SqlCommand("SELECT * FROM vw_AdminviewallInterviewer", conn);
            SqlDataAdapter da = new SqlDataAdapter(sqlcom);
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb= ds.Tables[0];
        }
        catch (Exception ex)
        {
            
        }
        finally 
        {
            connClose();
        }
        return tb;
    }

    public DataTable AdminSearchByInterviewID(string _InterviewerID) {
        DataTable tb = new DataTable();
        try
        {
            connOpen();
            SqlCommand sqlcom = new SqlCommand("EXECUTE AdminsearchByinterviewID  @InterviewerID",conn);
            sqlcom.Parameters.AddWithValue("@InterviewerID", _InterviewerID);
            SqlDataAdapter da = new SqlDataAdapter(sqlcom);
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception ex)
        {

        }
        finally 
        {
            connClose();
        }
        return tb;
    }
    public DataTable AddminsearchByFulName(string _Fullname) 
    {
        DataTable tb = new DataTable();
        try
        {
            connOpen();
            SqlCommand sqlcom = new SqlCommand("EXECUTE AdminSearchByFulName @FullName",conn);
            sqlcom.Parameters.AddWithValue("@FullName", _Fullname);
            SqlDataAdapter da = new SqlDataAdapter(sqlcom);
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception ex)
        {
        }
        finally
        {

            connClose();
        }
        return tb;
    }
    public DataTable InterviewInfomation(string _InterviewID) {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand sqlcom = new SqlCommand("EXECUTE vw_Interviewer @InterviewID",conn);
            sqlcom.Parameters.AddWithValue("@InterviewID",_InterviewID);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(sqlcom);
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception ex)
        {
        }
        finally {
            connClose();
        }
        return tb;
    }
    public DataTable HRinformation(string _Username) {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand sqlcom = new SqlCommand("EXECUTE informationHR @UserName",conn);
            sqlcom.Parameters.AddWithValue("@UserName",_Username);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(sqlcom);
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception ex)
        {
        }
        return tb;
    }
    public bool ResetPassInterviewer(string _inteviewerID) {
        bool Result = false;
        DataTable tb = new DataTable();
        try
        {
            SqlCommand sqlcomm = new SqlCommand("EXECUTE ResetPasInterview @InterViewerID",conn);            
            sqlcomm.Parameters.AddWithValue("@InterViewerID",_inteviewerID);
            connOpen();
            SqlDataReader dr = sqlcomm.ExecuteReader();
            while(dr.Read())
            {
                Result = true;
            }
        }
        catch (Exception ex)
        {
        }
        finally 
        {
            connClose();
        }
        return Result;
    }
    public bool ResetPassHR(string _username)
    {
        bool Result = false;
        DataTable tb = new DataTable();
        try
        {
            SqlCommand sqlcomm = new SqlCommand("EXECUTE ResetPasHR @UserName", conn);
            sqlcomm.Parameters.AddWithValue("@UserName", _username);
            connOpen();
            SqlDataReader dr = sqlcomm.ExecuteReader();
            while (dr.Read())
            {
                Result = true;
            }
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return Result;
    }
    public DataTable AdminInformation(string _Username)
    {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand sqlcom = new SqlCommand("EXECUTE AdminInformation @UserName", conn);
            sqlcom.Parameters.AddWithValue("@UserName", _Username);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(sqlcom);
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return tb;
    }
    public bool UpdateAddmin(string _Fulname, string _Address, string _phone, DateTime _birth, string _Email, string _username)
    {
        bool resurt = false;
        try 
	{
        SqlCommand sqlcom = new SqlCommand("EXECUTE UpdateInformation @FullName,@Address,@Phone,@BirthOfDate,@EmailID , @UserName", conn);
       
        sqlcom.Parameters.AddWithValue("@FullName", _Fulname);
        sqlcom.Parameters.AddWithValue("@Address", _Address);
        sqlcom.Parameters.AddWithValue("@Phone", _phone);
        sqlcom.Parameters.AddWithValue("@BirthOfDate", _birth);
        sqlcom.Parameters.AddWithValue("@EmailID", _Email);
        sqlcom.Parameters.AddWithValue("@UserName", _username);
        connOpen();
        int check = sqlcom.ExecuteNonQuery();
            if(check > 0){
                resurt = true;
            }
	}
	catch (Exception ex)
	{
	}finally
        {
        connClose();
        }
        return resurt;
    }

    //Page VacancyManager
    public DataSet LoadVacancyInfor()
    {
        DataSet ds = new DataSet();
        try
        {
            SqlCommand comm = new SqlCommand("Select * from LoadDataVacancy", conn);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(ds);
        }
        catch (Exception ex)
        {
        }
        return ds;
    }


    //Page VacancyDetail
    public DataTable LoadInformationVacancyFollowID(string _VacancyID)
    {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnInformationVacancyApplicant @ID", conn);
            comm.Parameters.AddWithValue("@ID", _VacancyID);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return tb;
    }
    public string ReturnEmployeeName(string _ID)
    {
        string Name = "";
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnEmployeeName @ID", conn);
            comm.Parameters.AddWithValue("@ID", _ID);
            connOpen();
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                Name = dr.GetString(0);
            }
        }
        catch (Exception ex)
        {
        }
        return Name;
    }

    //Page AdminViewEmployeeInfor
    public DataTable LoadDataEmployee(string _User)
    {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnInforEmployeeFollowID @ID", conn);
            comm.Parameters.AddWithValue("@ID", _User);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return tb;
    }

    //Page List Applicant Hired
    public DataTable LoadDataListHired(string _VacancyID)
    {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnApplicantHired @ID", conn);
            comm.Parameters.AddWithValue("@ID", _VacancyID);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception ex)
        {
        }
        return tb;
    }
    public bool AddnewHR(string _Username ,string _Fulname,string _Address,string _phone,DateTime _birth,string _Email) {
        bool resurt = false;
        try
        {

            SqlCommand sqlcom = new SqlCommand("EXECUTE AddnewHR @UserName,'7C4A8D09CA3762AF61E59520943DC26494F8941B',@FullName,@Address,@Phone,@BirthOfDate,@EmailID,0", conn);
            sqlcom.Parameters.AddWithValue("@UserName", _Username);
            sqlcom.Parameters.AddWithValue("@FullName", _Fulname);
            sqlcom.Parameters.AddWithValue("@Address", _Address);
            sqlcom.Parameters.AddWithValue("@Phone", _phone);
            sqlcom.Parameters.AddWithValue("@BirthOfDate", _birth);
            sqlcom.Parameters.AddWithValue("@EmailID", _Email);
            connOpen();
            int check = sqlcom.ExecuteNonQuery();
            if (check > 0)
            {
                resurt = true;
            }
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return resurt;
    }
    public bool AddnewInterviewer(string _Username, string _Fulname, string _Address, string _phone, DateTime _birth, string _Email)
    {
        bool resurt = false;
        try
        {

            SqlCommand sqlcom = new SqlCommand("EXECUTE AddnewInterviewer @UserName,'7C4A8D09CA3762AF61E59520943DC26494F8941B',@FullName,@Address,@Phone,@BirthOfDate,@EmailID", conn);
            sqlcom.Parameters.AddWithValue("@UserName", _Username);
            sqlcom.Parameters.AddWithValue("@FullName", _Fulname);
            sqlcom.Parameters.AddWithValue("@Address", _Address);
            sqlcom.Parameters.AddWithValue("@Phone", _phone);
            sqlcom.Parameters.AddWithValue("@BirthOfDate", _birth);
            sqlcom.Parameters.AddWithValue("@EmailID", _Email);
            connOpen();
            int check = sqlcom.ExecuteNonQuery();
            if (check > 0)
            {
                resurt = true;
            }
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return resurt;
    }


    //Page add new Employee
    private string HRIDLast()
    {
        string VacancyID = "";
        try
        {
            SqlCommand comm = new SqlCommand("Select * from ReturnHRID", conn);
            connOpen();
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(ds);
            DataTable tb = ds.Tables[0];
            if (tb.Rows.Count > 0)
            {
                VacancyID = tb.Rows[tb.Rows.Count - 1]["UserName"].ToString();
            }
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        finally
        {
            connClose();
        }
        return VacancyID;
    }
    public string HRIDNew()
    {
        string VacancyID = HRIDLast();
        try
        {
            if (!VacancyID.Equals(""))
            {
                char[] cID = VacancyID.ToArray();
                int n = VacancyID.Count() - 1;
                VacancyID = "";
                for (int i = n; i >= 0; i--)
                {
                    try
                    {
                        int iu = Convert.ToInt32(cID[i].ToString());
                        if (iu < 9)
                        {
                            iu++;
                            VacancyID += iu;
                            for (int j = i - 1; j >= 0; j--)
                            {
                                VacancyID += cID[j].ToString();
                            }
                            break;
                        }
                        else
                        {
                            if (i == 1)
                            {
                                VacancyID += "01";
                            }
                            else
                            {
                                iu = 0;
                                VacancyID += iu;
                            }
                        }
                    }
                    catch (Exception)
                    {
                        VacancyID += cID[i].ToString();
                    }
                }
                char[] cUser = VacancyID.ToArray();
                VacancyID = "";
                for (int i = n; i >= 0; i--)
                {
                    VacancyID += cUser[i].ToString();
                }
            }
            else
            {
                VacancyID = "H0001";
            }
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        return VacancyID;
    }

    private string InterviewerIDLast()
    {
        string VacancyID = "";
        try
        {
            SqlCommand comm = new SqlCommand("Select * from ReturnInterviewerID", conn);
            connOpen();
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(ds);
            DataTable tb = ds.Tables[0];
            if (tb.Rows.Count > 0)
            {
                VacancyID = tb.Rows[tb.Rows.Count - 1]["InterViewerID"].ToString();
            }
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        finally
        {
            connClose();
        }
        return VacancyID;
    }
    public string InterviewerIDNew()
    {
        string VacancyID = InterviewerIDLast();
        try
        {
            if (!VacancyID.Equals(""))
            {
                char[] cID = VacancyID.ToArray();
                int n = VacancyID.Count() - 1;
                VacancyID = "";
                for (int i = n; i >= 0; i--)
                {
                    try
                    {
                        int iu = Convert.ToInt32(cID[i].ToString());
                        if (iu < 9)
                        {
                            iu++;
                            VacancyID += iu;
                            for (int j = i - 1; j >= 0; j--)
                            {
                                VacancyID += cID[j].ToString();
                            }
                            break;
                        }
                        else
                        {
                            if (i == 1)
                            {
                                VacancyID += "01";
                            }
                            else
                            {
                                iu = 0;
                                VacancyID += iu;
                            }
                        }
                    }
                    catch (Exception)
                    {
                        VacancyID += cID[i].ToString();
                    }
                }
                char[] cUser = VacancyID.ToArray();
                VacancyID = "";
                for (int i = n; i >= 0; i--)
                {
                    VacancyID += cUser[i].ToString();
                }
            }
            else
            {
                VacancyID = "P0001";
            }
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        return VacancyID;
    }
}